from ..model import db, session, User, Transaction, and_, datetime


def chg_trasc_do(name, trasc_type, money, event, trasc_num=None, typ="add"):
    if name == '':
        return {
            'error': 'true',
            'message': '姓名不能为空'
        }
    elif trasc_type == '':
        return {
            'error': 'true',
            'message': '交易类型不能为空'
        }
    elif money == '':
        return {
            'error': 'true',
            'message': '金额不能为空'
        }
    elif session.query(User).filter(User.name == name).first() is None:
        return {
            'error': 'true',
            'message': '用户不存在'
        }
    else:
        user_id = session.query(User).filter(User.name == name).first().usrId
        if typ == 'add':
            session.add(Transaction(trascUsrId=user_id, trascType=trasc_type, money=money, event=event))
        elif typ == 'upd':
            session.query(Transaction).filter(Transaction.trascFlowNum == trasc_num).update(
                {
                    'trascUsrId': user_id,
                    'trascType': trasc_type,
                    'money': money,
                    'event': event,
                    'trascTm': datetime.now()
                }
            )
        query = session.query(User).filter(User.usrId == user_id).first()
        if trasc_type == '收入':
            query.ttlInc += int(money)
        else:
            query.ttlSpd += int(money)
        if query.ttlInc > query.ttlSpd:
            query.trascStatus = '已盈利'
        elif query.ttlInc < query.ttlSpd:
            query.trascStatus = '已亏损'
        else:
            query.trascStatus = '盈亏平衡'
        db.commit(session)
        return {
            'error': 'false',
            'message': '操作成功'
        }


def get_trasc_list_do(name, trasc_type, start_time, end_time, page_num):
    query = session.query(
            Transaction.trascFlowNum,
            User.name,
            Transaction.trascType,
            Transaction.money,
            Transaction.event
        ).join(
            User,
            Transaction.trascUsrId == User.usrId
        )
    results = None
    offset = (page_num - 1) * 10
    if name == '' and trasc_type == '' and start_time == '' and end_time == '':
        results = query.order_by(Transaction.trascTm.desc()).offset(offset).limit(10).all()
    elif name != '' and trasc_type == '' and start_time == '' and end_time == '':
        results = query.filter(User.name == name).order_by(
            Transaction.trascTm.desc()).offset(offset).limit(10).all()
    elif name == '' and trasc_type != '' and start_time == '' and end_time == '':
        results = query.filter(Transaction.trascType == trasc_type).order_by(
            Transaction.trascTm.desc()).offset(offset).limit(10).all()
    elif name == '' and trasc_type == '' and start_time != '' and end_time != '':
        results = query.filter(Transaction.trascTm.between(start_time, end_time)).order_by(
            Transaction.trascTm.desc()).offset(offset).limit(10).all()
    elif name != '' and trasc_type != '' and start_time == '' and end_time == '':
        results = query.filter(and_(
            User.name == name,
            Transaction.trascType == trasc_type
        )).order_by(Transaction.trascTm.desc()).offset(offset).limit(10).all()
    elif name == '' and trasc_type != '' and start_time != '' and end_time != '':
        results = query.filter(and_(
            Transaction.trascType == trasc_type,
            Transaction.trascTm.between(start_time, end_time)
        )).order_by(Transaction.trascTm.desc()).offset(offset).limit(10).all()
    elif name != '' and trasc_type == '' and start_time != '' and end_time != '':
        results = query.filter(and_(
            User.name == name,
            Transaction.trascTm.between(start_time, end_time)
        )).order_by(Transaction.trascTm.desc()).offset(offset).limit(10).all()
    elif name != '' and trasc_type != '' and start_time != '' and end_time != '':
        results = query.filter(and_(
            User.name == name,
            Transaction.trascType == trasc_type,
            Transaction.trascTm.between(start_time, end_time)
        )).order_by(Transaction.trascTm.desc()).offset(offset).limit(10).all()
    data_count = len(session.query(Transaction).all())
    data = [
        {
            'trascFlowNum': result[0],
            'name': result[1],
            'trascType': result[2],
            'money': result[3],
            'event': result[4]
        }
        for result in results
    ]
    return {
        'dataCount': data_count,
        'dataList': data
    }


def get_trasc_dtl_do(trasc_num, name):
    result = session.query(
        User.name,
        Transaction.trascFlowNum,
        Transaction.trascType,
        Transaction.money,
        Transaction.event,
        Transaction.trascTm
    ).join(
        User,
        Transaction.trascUsrId == User.usrId
    ).filter(and_(
        User.name == name,
        Transaction.trascFlowNum == trasc_num
    )).first()
    return {
        'name': result[0],
        'trascFlowNum': result[1],
        'trascType': result[2],
        'money': result[3],
        'event': result[4],
        'trascTm': result[5].strftime('%Y-%m-%d %H:%M:%S')
    }


def delete_trasc_do(trasc_num):
    session.query(Transaction).filter(Transaction.trascFlowNum == trasc_num).delete()
    db.commit(session)
    return {
        'error': 'false',
        'message': '操作成功'
    }
